import numpy as np
import pandas as pd

brand_file_path = '../../data/raw data/餐饮连锁品牌数据.xlsx'
sheet_names=['门店信息','菜品信息','销售记录','顾客评价']
sheet_name=sheet_names[0]
df_store = pd.read_excel(brand_file_path, sheet_name)

print("\n【空值统计】")
print(df_store.isnull().sum())
print("======================")
missing_rate = df_store.isnull().mean().sort_values(ascending=False)
print("\n【空值比例（Top 10）】")
print(missing_rate.head(10))
print("======================")
before_rows = df_store.shape[0]
df_store.dropna(inplace=True)
after_rows = df_store.shape[0]
print(f"\n【空值处理】已删除 {before_rows - after_rows} 行包含空值的数据。")

print("\n【空值处理后验证】")
print(df_store.isnull().sum().sum())
print('======================')
print('======================')

print("\n【重复值检测】")
print(df_store.duplicated().sum())
print('================================')
if df_store.duplicated().sum() > 0:
    print(df_store[df_store.duplicated()])
df_store.drop_duplicates(inplace=True)
print("\n【重复值处理后验证】")
print(df_store.duplicated().sum())
print('======================')
print('======================')
print('=============================')

print("\n【数值列统计描述】")
print(df_store.describe())

print('员工数量--------------------------------------')
outer='员工数量'
if outer in df_store.columns:
    mean_val = df_store[outer].mean()
    std_val = df_store[outer].std()
    upper = mean_val + 3 * std_val
    lower = 1
    outliers = df_store[(df_store[outer] > upper) | (df_store[outer] < lower)]
    print(f"\n检测到异常值数量：{len(outliers)}")
    median_val = df_store[outer].median()
    df_store.loc[(df_store[outer] > upper) | (df_store[outer] < lower), outer] = median_val
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_val}。")

print('店面面积--------------------------------------')
outer='店面面积'
if outer in df_store.columns:
    upper = 1000
    lower = 10
    outliers = df_store[(df_store[outer] > upper) | (df_store[outer] < lower)]
    print(f"\n检测到异常值数量：{len(outliers)}")
    median_val = df_store[outer].median()
    df_store.loc[(df_store[outer] > upper) | (df_store[outer] < lower), outer] = median_val
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_val}。")

print('======================================')
print('======================================')

# clean_path = '../../data/餐饮连锁品牌数据_门店信息_cleaned.xlsx'
# df_store.to_excel(clean_path, index=False)
# print(f"\n✅ 数据清洗完成，已保存至 {clean_path}")
